package com.kpmg.datalake.reportanalysis.service.impl;

import com.alibaba.fastjson.JSONObject;
import com.csvreader.CsvReader;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.kpmg.datalake.common.constants.Constants;
import com.kpmg.datalake.common.dynamicdatasource.DataSourceStoreService;
import com.kpmg.datalake.common.dynamicdatasource.DynamicDS;
import com.kpmg.datalake.common.enums.ExportFormatEnum;
import com.kpmg.datalake.common.enums.ReportTypeEnum;
import com.kpmg.datalake.common.exception.CustomException;
import com.kpmg.datalake.common.handler.QueryRequestQueue;
import com.kpmg.datalake.common.model.AuditRequest;
import com.kpmg.datalake.common.model.DataExportCondition;
import com.kpmg.datalake.common.model.QueryRequestParam;
import com.kpmg.datalake.common.service.IProjectRptSqlService;
import com.kpmg.datalake.common.utils.ExcelUtils;
import com.kpmg.datalake.common.utils.ResponseUtils;
import com.kpmg.datalake.common.utils.Utils;
import com.kpmg.datalake.common.vo.KeyAndValueDTO;
import com.kpmg.datalake.common.vo.ServerResponse;
import com.kpmg.datalake.db.dao.RqsAccbookMapper;
import com.kpmg.datalake.db.dao.RqsDwldListMapper;
import com.kpmg.datalake.db.dao.RqsListMapper;
import com.kpmg.datalake.db.model.*;
import com.kpmg.datalake.reportanalysis.dao.DataMapper;
import com.kpmg.datalake.reportanalysis.model.ExportCheck;
import com.kpmg.datalake.reportanalysis.service.DataService;
import info.monitorenter.cpdetector.io.*;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.nio.charset.Charset;
import java.text.MessageFormat;
import java.util.*;

/**
 * 数据相关操作服务
 *
 * @author Tony
 */
@Service
public class DataServiceImpl implements DataService {
    // 日志
    private static final Logger logger = LoggerFactory.getLogger(DataServiceImpl.class);
    // 注入服务
    @Autowired
    private DataMapper dataMapper;
    @Autowired
    private RqsListMapper rqsListMapper;
    @Autowired
    private RqsAccbookMapper rqsAccbookMapper;
    @Autowired
    private RqsDwldListMapper qsDwldListMapper;
    // 常数信息
    private static final String USER_HOME = "user.home";
    private static final String CSV_FORMART = "CSV";
    private static final String XLS_FORMART = "XLS";
    private static final String XLSX_FORMART = "XLSX";
    private static final String ACCBOOKCD = "accbookCd";
    private static final String ACCBOOKID = "accbookId";
    private static final String ACCBOOKNM = "accbookNm";
    private static final String WRONGFORMAT = "wrong format:{}";
    private static final String PARSEERROR = "文件解析异常：{}";
    @Resource
    private DataSourceStoreService dataSourceStoreService;
    @Resource
    private IProjectRptSqlService projectRptSqlService;
    /**
     * 依据用户ID获取查询请求列表
     */
    @Override
    public JSONObject getRequestList(String projectId, String searchValue, String orderColumn, String orderDir,
                                     int start, int size) {
        List<AuditRequest> auditRequests = new ArrayList<>();
        JSONObject result = new JSONObject();
        // 分页查询请求列表
        int page = start < 0 ? 1 : start / size + 1;
        PageHelper.startPage(page, size);
        List<RqsList> requestList = rqsListMapper.selectBySearch(projectId, searchValue, orderColumn,
                orderDir.toUpperCase());
        PageInfo<RqsList> requestPage = new PageInfo<>(requestList);
        long total = requestPage.getTotal();
        // 组装请求列表数据
        String formatStr = "{0}" + Constants.YEAR + ",{1},{2}";
        this.formatAuditRequest(auditRequests, requestList, formatStr);
        // 设置返回值
        result.put("data", auditRequests);
        result.put("recordsTotal", total);
        result.put("recordsFiltered", total);
        return result;
    }

    /**
     * @Author:Shirelyyuan
     * @Description: 提取出公共方法，解决代码重复
     * @Date:
     *
     */
    private void formatAuditRequest(List<AuditRequest> auditRequest, List<RqsList> requestList, String formatStr){
        requestList.forEach(request -> {
            AuditRequest item = new AuditRequest();
            item.setRequestNo(request.getRqsListId());
            item.setCreateDate(Utils.formatDateTime(request.getCrtDt()));
            item.setBookList(request.getBookList());
            String param = MessageFormat.format(formatStr, request.getAcgAnul(), request.getRptTpNm(),
                request.getFileFmtNm());
            item.setRequestParam(param);
            item.setStatus(request.getRqsSt());
            item.setFilePath(request.getFilePath());
            item.setComment(request.getRqsMemo());
            auditRequest.add(item);
        });
    }
    /**
     * 通过主键查询信息
     *
     * @param rqsListId rqsListId
     * @return RqsList
     */
    @Override
    public RqsList selectRqsListById(String rqsListId) {
        return rqsListMapper.selectByPrimaryKey(rqsListId);
    }

    /**
     * 依据用户ID获取查询请求列表
     */
    @Override
    public ServerResponse<PageInfo> getRequestList2(String projectId, String searchValue, String orderColumn, String orderDir, int start, int size) {
        List<AuditRequest> result = new ArrayList<>();
        PageHelper.startPage(start, size);
        List<RqsList> requestList = rqsListMapper.selectBySearch(projectId, searchValue, orderColumn, orderDir.toUpperCase());
        PageInfo resultPage = new PageInfo<>(requestList);
        // 组装请求列表数据
        String formatStr = "{0}" + Constants.YEAR + ",{1},{2}";
        this.formatAuditRequest(result, requestList, formatStr);
        // 设置返回值
        resultPage.setList(result);
        return ServerResponse.createBySuccess("获取查询与导出列表成功", resultPage);
    }

    /**
     * 获取会计年月
     */
    @Override
    @DynamicDS("projectId")
    public JSONObject getAccYearMonth(String projectId) {
        JSONObject result = new JSONObject();
        // 年份列表
        Set<String> yearList = new LinkedHashSet<>();
        // 月份列表
        Set<String> monthList = new LinkedHashSet<>();
        // 查询会计年月信息
        List<Map<String, String>> records = dataMapper.queryAccprd();
        records.forEach(record -> {
            yearList.add(record.get("accYear".toUpperCase()));
            monthList.add(record.get("accMonth".toUpperCase()));
        });
        result.put("yearList", yearList);
        result.put("monthList", monthList);
        return result;
    }

    /**
     * 导出所有账套信息
     */
    @Override
    @DynamicDS("projectId")
    public void exportAllBookSet(String projectId, SysUsr user, HttpServletRequest request,
                                 HttpServletResponse response) {
        // 文件信息
        String fileName = Constants.KPMG_GLOR_BOOK_FILE_NAME;
        Project project = Utils.getProject(projectId, user);
        fileName = project.getEntityBasic().getEntShrtnm() + "-" + fileName;
        String sheetName = "所有账套类型";
        // 生成账套文件
        Map<String, String> fieldMap = new LinkedHashMap<>();
        String[] headers = Constants.KPMG_GLOR_BOOK_CATALOG;
        for (String header : headers) {
            fieldMap.put(header, header);
        }
        // 查询账套数据
        List<Map<String, Object>> records = dataMapper.queryGlorBook();
        // 导出Excel文件
        ExcelUtils.listToExcel(records, fieldMap, fileName, sheetName, request, response);
    }

    /**
     * 上传筛选账套
     */
    @Override
    public KeyAndValueDTO<Boolean, String> uploadBookSet(SysUsr user, MultipartFile bookSetFile) {
        KeyAndValueDTO<Boolean, String> result = new KeyAndValueDTO<>(false, "");
        try {
            // 设置上传账套路径
            String basePath = "upload" + File.separator;
            String personalPath = basePath + user.getUsrId() + File.separator;
            String uploadDir = System.getProperty(USER_HOME) + File.separator + personalPath;
            // 创建账套文件路径
            File dir = new File(uploadDir);
            if (!dir.exists()) {
                dir.mkdirs();
            }
            String originalName = bookSetFile.getOriginalFilename();
            String fileName = Constants.KPMG_GLOR_SELECTED_BOOK_FILE_NAME + "."
                    + originalName.substring(originalName.lastIndexOf('.') + 1);
            File serverFile = new File(uploadDir + fileName);
            bookSetFile.transferTo(serverFile);
            // 检查上传数据
            if (checkFile(serverFile.getAbsolutePath())) {
                result.setKey(true);
                result.setValue(serverFile.getAbsolutePath());
            } else {
                logger.error("{}异常", "上传筛选账套数据");
                result.setValue("上传筛选账套数据异常");
            }
        } catch (Exception e) {
            logger.error("上传筛选账套数据异常:{}", e.getMessage(), e);
            result.setValue("上传筛选账套数据异常" + e.getLocalizedMessage());
        }
        return result;
    }

    /**
     * 校验XLS文件
     */
    private boolean checkXlsFile(String bookLocation, FileInputStream inputStream, int totalCols) {
        boolean success = true;
        try {
            HSSFWorkbook wb = new HSSFWorkbook(inputStream);
            HSSFSheet sheet = wb.getSheetAt(0);
            success = sheet.getLastRowNum() != 0;
            int rowCnt = 1;
            for (Row row : sheet) {
                success = this.checkWorkbookCell(rowCnt, row, bookLocation, totalCols);
                rowCnt++;
            }
        } catch (Exception e) {
            logger.error("文件解析异常:{}", e.getMessage(), e);
            throw new CustomException(e.getMessage());
        }
        return success;
    }

    /**
     * @Author:Shirelyyuan
     * @Description: 解决代码重复
     * @Date:
     *
     */
    private Boolean checkWorkbookCell(int rowCnt, Row row, String bookLocation, int totalCols){
        boolean success = true;
        if (rowCnt != 1) {
            for (int i = 0; i < totalCols; i++) {
                Cell cell = row.getCell(i);
                success = checkCell(bookLocation, cell);
            }
        }
        return success;
    }

    /**
     * 校验文件合法性
     */
    private boolean checkFile(String bookLocation) throws IOException {
        // 获取文件对象
        String fileName = bookLocation.substring(bookLocation.lastIndexOf(File.separator) + 1);
        String suffix = fileName.substring(fileName.lastIndexOf('.') + 1);
        File bookFile = new File(bookLocation);
        // 校验标识
        boolean success = true;
        try (FileInputStream inputStream = new FileInputStream(bookFile);) {
            int totalCols = 3;
            // 校验CSV文件
            if (CSV_FORMART.equalsIgnoreCase(suffix)) {
                success = checkCsvFile(bookFile, success, inputStream);
            } else if (XLS_FORMART.equalsIgnoreCase(suffix)) {
                // 校验XLS文件
                success = checkXlsFile(bookLocation, inputStream, totalCols);
            } else if (XLSX_FORMART.equalsIgnoreCase(suffix)) {
                // 校验XLSX文件
                success = checkXlsxFile(bookLocation, inputStream, totalCols);
            }
        } catch (Exception e) {
            logger.error(PARSEERROR, e.getMessage(), e);
            success = false;
        }
        return success;
    }

    private boolean checkCsvFile(File bookFile, boolean success, FileInputStream inputStream) throws IOException {
        CsvReader reader = this.getCvsReader(bookFile, inputStream);
        reader.readHeaders();
        String[] headers = reader.getHeaders();
        while (reader.readRecord()) {
            // 账套编码
            String accbookCdValue = reader.get(headers[0]) == null ? "" : reader.get(headers[0]);
            // 账套主键
            String accbookIdValue = reader.get(headers[1]) == null ? "" : reader.get(headers[1]);
            // 账套名称
            String accbookNmValue = reader.get(headers[2]) == null ? "" : reader.get(headers[2]);
            if (StringUtils.isEmpty(accbookCdValue.trim()) || StringUtils.isEmpty(accbookIdValue.trim())
                    || StringUtils.isEmpty(accbookNmValue.trim())) {
                success = false;
                break;
            }
        }
        return success;
    }

    /**
     * @Author:Shirelyyuan
     * @Description:提取重复代码
     * @Date:
     *
     */
    private CsvReader getCvsReader(File bookFile, FileInputStream inputStream) throws IOException{
        CodepageDetectorProxy detector = CodepageDetectorProxy.getInstance();
        detector.add(new ParsingDetector(false));
        detector.add(JChardetFacade.getInstance());
        detector.add(ASCIIDetector.getInstance());
        detector.add(UnicodeDetector.getInstance());
        Charset charset = detector.detectCodepage(bookFile.toURI().toURL());
        String charsetName = charset != null ? charset.name() : "UTF-8";
        return new CsvReader(inputStream, ',', Charset.forName(charsetName));
    }

    /**
     * 校验XLSX文件
     */
    private boolean checkXlsxFile(String bookLocation, FileInputStream inputStream, int totalCols) {
        boolean success = true;
        try {
            XSSFWorkbook wb = new XSSFWorkbook(inputStream);
            XSSFSheet sheet = wb.getSheetAt(0);
            success = sheet.getLastRowNum() != 0;
            int rowCnt = 1;
            for (Row row : sheet) {
                success = this.checkWorkbookCell(rowCnt, row, bookLocation, totalCols);
                rowCnt++;
            }
        } catch (Exception e) {
            logger.error("文件解析异常:{}", e.getMessage(), e);
            throw new CustomException(e.getMessage());
        }
        return success;
    }

    /**
     * 校验单元格内容
     */
    private boolean checkCell(String bookLocation, Cell cell) {
        boolean success = true;
        String cellValue = "";
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                cellValue = cell.getRichStringCellValue().getString();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            default:
                logger.error(WRONGFORMAT, bookLocation);
                break;
        }
        success = !StringUtils.isEmpty(cellValue.trim());
        return success;
    }

    /**
     * 获取会计月份
     */
    @DynamicDS("projectId")
    @Override
    public List<String> getAccMonth(String projectId, String monthBegin, String monthEnd) {
        return dataMapper.queryAccmonth(monthBegin, monthEnd);
    }

    /**
     * 数据导出请求
     */
    @Transactional
    @Override
    public String exportRequest(SysUsr user, DataExportCondition condition, String bookLocation, String projectId) {
        String requestNo;
        // 获取项目信息
        Project project = Utils.getProject(projectId, user);
        // 设置结果文件路径
        String basePath = "result" + File.separator + projectId + File.separator;
        String personalPath = System.getProperty(USER_HOME) + File.separator + basePath + user.getUsrId()
                + File.separator;
        try {
            File baseLocation = new File(personalPath);
            if (!baseLocation.exists()) {
                baseLocation.mkdirs();
            }
            // 读取筛选账套写入RQS_ACCBOOK
            List<Map<String, String>> bookRecords = getBookRecords(bookLocation);
            List<String> bookIds = new ArrayList<>();
            bookRecords.forEach(bookRecord -> {
                bookIds.add(bookRecord.get(ACCBOOKID));
            });
            // 校验请求是否存在
            List<HashMap<String, String>> requestNos = rqsListMapper.checkRequestList(condition.getYear(),
                    condition.getMonthBegin(), condition.getMonthEnd(), condition.getExportType(),
                    condition.getExportFormat(), projectId);
            if (requestNos != null) {
                for (Map<String, String> requestParam : requestNos) {
                    String status = requestParam.get("RQS_ST");
                    if ("执行失败".equals(status)) {
                        continue;
                    }
                    String rqsListId = requestParam.get("RQS_LIST_ID");
                    List<String> existBookIds = rqsAccbookMapper.checkRqsAccbook(rqsListId);
                    if (existBookIds.size() == bookIds.size() && existBookIds.containsAll(bookIds)) {
                        return "null+"+rqsListId;
                    }
                }
            }
            int requestCnt = rqsListMapper.getUserRqsCount(user.getUsrId());
            // 写入请求表RQS_LIST
            requestNo = Utils.getRequestNo();
            RqsList rqs = new RqsList();
            rqs.setRqsListId(requestNo);
            rqs.setAcgAnul(condition.getYear());
            rqs.setAccprdBegdt(condition.getMonthBegin());
            rqs.setAccprdEnddt(condition.getMonthEnd());
            rqs.setRptTpId(condition.getExportType());
            rqs.setRptTpNm(ReportTypeEnum.getDisplayByCode(condition.getExportType()));
            rqs.setFileFmtId(condition.getExportFormat());
            rqs.setFileFmtNm(ExportFormatEnum.getDisplayByCode(condition.getExportFormat()));
            rqs.setRqsSt("正在执行");
            rqs.setDelInd(false);
            rqs.setUsrId(user.getUsrId());
            rqs.setPrjId(projectId);
            rqs.setCrtUsrId(user.getUsrId());
            rqs.setCrtDt(new Date());
            rqs.setRqsCd(user.getUsrAcc() + Utils.formatRequestCd(requestCnt));
            rqs.setRqsMemo(condition.getExportComment());
            rqsListMapper.insertSelective(rqs);
            logger.info("{}写入请求表", requestNo);
            // 写入请求账簿表
            bookRecords.forEach(bookRecord -> {
                RqsAccbook book = new RqsAccbook();
                book.setAccbookCd(bookRecord.get(ACCBOOKCD));
                book.setAccbookId(bookRecord.get(ACCBOOKID));
                book.setAccbookNm(bookRecord.get(ACCBOOKNM));
                book.setRqsListId(requestNo);
                rqsAccbookMapper.insertSelective(book);
            });
            logger.info("{}读取筛选账套写入RQS_ACCBOOK", requestNo);
            // 加入到请求队列
            QueryRequestParam requestParam = new QueryRequestParam();
            requestParam.setDatabase(dataSourceStoreService.getMemoryDataSourceById(projectId));
            requestParam.setCondition(condition);
            requestParam.setSqlText(projectRptSqlService.getSqlTextByInfo(projectId, condition.getExportType(), condition.getExportFormat()));
            requestParam.setRequestNo(requestNo);
            requestParam.setRqsAccbookMapper(rqsAccbookMapper);
            requestParam.setRqsListMapper(rqsListMapper);
            requestParam.setPersonalPath(personalPath);
            requestParam.setProject(project);
            // 加入请求队列
            QueryRequestQueue.getInstance().put(requestParam);
            logger.info("{}加入到请求队列", requestNo);
        } catch (Exception e) {
            logger.error("数据导出请求时异常:{}", e.getMessage(), e);
            throw new CustomException(e);
        }
        return requestNo;
    }

    /**
     * 获取账套信息
     */
    private List<Map<String, String>> getBookRecords(String bookLocation) throws FileNotFoundException {
        // 账套记录
        List<Map<String, String>> bookRecords = new LinkedList<>();
        // 账套文件信息
        String fileName = bookLocation.substring(bookLocation.lastIndexOf(File.separator) + 1);
        String suffix = fileName.substring(fileName.lastIndexOf('.') + 1);
        File bookFile = new File(bookLocation);
        String errorMsg = "文件数据错误";
        try (FileInputStream inputStream = new FileInputStream(bookFile)) {
            if (CSV_FORMART.equalsIgnoreCase(suffix)) {
                bookRecords = getCsvBookRecords(bookFile, errorMsg, inputStream);
            } else if (XLS_FORMART.equalsIgnoreCase(suffix)) {
                bookRecords = getXlsBookRecords(bookLocation, errorMsg, inputStream);
            } else if (XLSX_FORMART.equalsIgnoreCase(suffix)) {
                bookRecords = getXlsxBookRecords(bookLocation, errorMsg, inputStream);
            }
        } catch (Exception e) {
            logger.error(PARSEERROR, e.getMessage(), e);
            if (errorMsg.equals(e.getMessage())) {
                throw new CustomException("文件数据错误! ");
            } else {
                throw new CustomException("非法文件格式！");
            }
        }
        return bookRecords;
    }

    /**
     * 获取Csv文件记录
     */
    private List<Map<String, String>> getCsvBookRecords(File bookFile, String errorMsg, FileInputStream inputStream)
            throws IOException {
        List<Map<String, String>> bookRecords = new ArrayList<>();
        Map<String, String> bookRecord;
        // 解析CSV文件

        CsvReader reader = this.getCvsReader(bookFile, inputStream);
        reader.readHeaders();
        String[] headers = reader.getHeaders();
        while (reader.readRecord()) {
            bookRecord = new LinkedHashMap<>();
            // 账套编码
            String accbookCdValue = reader.get(headers[0]) == null ? "" : reader.get(headers[0]);
            // 账套主键
            String accbookIdValue = reader.get(headers[1]) == null ? "" : reader.get(headers[1]);
            // 账套名称
            String accbookNmValue = reader.get(headers[2]) == null ? "" : reader.get(headers[2]);
            if (StringUtils.isEmpty(accbookCdValue.trim()) || StringUtils.isEmpty(accbookIdValue.trim())
                    || StringUtils.isEmpty(accbookNmValue.trim())) {
                throw new CustomException(errorMsg);
            }
            bookRecord.put(ACCBOOKCD, accbookCdValue);
            bookRecord.put(ACCBOOKID, accbookIdValue);
            bookRecord.put(ACCBOOKNM, accbookNmValue);
            bookRecords.add(bookRecord);
        }
        return bookRecords;
    }

    /**
     * 获取Xls文件记录
     */
    private List<Map<String, String>> getXlsBookRecords(String bookLocation, String errorMsg,
                                                        FileInputStream inputStream) {
        List<Map<String, String>> bookRecords = new ArrayList<>();
        List<Map<String, String>> bookRowRecords = new ArrayList<>();
        try {
            HSSFWorkbook wb = new HSSFWorkbook(inputStream);
            HSSFSheet sheet = wb.getSheetAt(0);
            int rowCnt = 1;
            for (Row row : sheet) {
                bookRowRecords = getBookResults(bookLocation, errorMsg, rowCnt, row);
            	if(bookRowRecords.isEmpty()) {
            		bookRecords = bookRowRecords;
            	}else {
            		bookRecords.add(bookRowRecords.get(0));
            	}
            	
                rowCnt++;
            }
        } catch (Exception e) {
            logger.error(PARSEERROR, e.getMessage(), e);
        }
        return bookRecords;
    }

    /**
     * 获取Xlsx文件记录
     */
    private List<Map<String, String>> getXlsxBookRecords(String bookLocation, String errorMsg,
                                                         FileInputStream inputStream) {
        List<Map<String, String>> bookRecords = new ArrayList<>();
        try {
            XSSFWorkbook wb = new XSSFWorkbook(inputStream);
            XSSFSheet sheet = wb.getSheetAt(0);
            int rowCnt = 1;
            for (Row row : sheet) {
                bookRecords = getBookResults(bookLocation, errorMsg, rowCnt, row);
                rowCnt++;
            }
        } catch (Exception e) {
            logger.error(PARSEERROR, e.getMessage(), e);
        }
        return bookRecords;
    }

    /**
     * 获取Xls/Xlsx文件记录
     */
    private List<Map<String, String>> getBookResults(String bookLocation, String errorMsg, int rowCnt, Row row) {
        List<Map<String, String>> bookRecords = new ArrayList<>();
        Map<String, String> bookRecord;
        if (rowCnt != 1) {
            int cellCnt = 1;
            bookRecord = new LinkedHashMap<>();
            for (Cell cell : row) {
                String cellValue = "";
                switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC:
                        cellValue = String.valueOf(cell.getNumericCellValue());
                        break;
                    case Cell.CELL_TYPE_STRING:
                        cellValue = cell.getRichStringCellValue().getString();
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        cellValue = String.valueOf(cell.getBooleanCellValue());
                        break;
                    default:
                        logger.error(WRONGFORMAT, bookLocation);
                        break;
                }
                if (StringUtils.isEmpty(cellValue.trim())) {
                    throw new CustomException(errorMsg);
                }
                if (cellCnt == 1) {
                    // 账套编码
                    bookRecord.put(ACCBOOKCD, cellValue);
                } else if (cellCnt == 2) {
                    // 账套主键
                    bookRecord.put(ACCBOOKID, cellValue);
                } else if (cellCnt == 3) {
                    // 账套名称
                    bookRecord.put(ACCBOOKNM, cellValue);
                }
                cellCnt++;
            }
            bookRecords.add(bookRecord);
        }
        return bookRecords;
    }

    /**
     * 记录用户下载
     */
    @Override
    public void logFileDownload(String userId, String requestNo) {
        RqsDwldList rqsDownload = new RqsDwldList();
        rqsDownload.setDwldListId(Utils.getGUID());
        rqsDownload.setRqsListId(requestNo);
        rqsDownload.setCrtUsrId(userId);
        rqsDownload.setCrtDt(new Date());
        qsDwldListMapper.insertSelective(rqsDownload);
    }

    /**
     * 刷新请求任务状态
     */
    @Override
    public void flushRequestStatus(boolean reboot) {
        // 获取请求中的任务
        List<RqsList> requests = rqsListMapper.getRunningRqs();
        if (reboot) {
            // 重启应用后，设置执行中的任务为执行失败
            requests.forEach(request -> {
                RqsList rqsList = new RqsList();
                rqsList.setRqsListId(request.getRqsListId());
                rqsList.setRqsSt("执行失败");
                rqsListMapper.updateByPrimaryKeySelective(rqsList);
            });
        } else {
            long maxTime = 6;
            // 任务超时(大于6小时)，设置执行中的任务为执行失败
            requests.forEach(request -> {
                Date now = new Date();
                long timeCost = (now.getTime() - request.getCrtDt().getTime()) / (60 * 60 * 1000);
                if (timeCost > maxTime) {
                    RqsList rqsList = new RqsList();
                    rqsList.setRqsListId(request.getRqsListId());
                    rqsList.setRqsSt("执行失败");
                    rqsListMapper.updateByPrimaryKeySelective(rqsList);
                }
            });
        }
    }

    private static final String[] CHECK_SHEET_TITLE = {"账套编码", "账套名称", "会计年度", "会计期间", "本期借方发生金额", "本期贷方发生金额", "借贷相差金额"};

    private static final String[] INTERSECTR_SHEET_TITLE = {"账套编码", "账套名称", "会计年度", "会计期间", "借方发生累计差额", "贷方发生累计差额"};

    /**
     * 校验类型 EXPRT_BTCH_ID: 源系统 => 数据文件
     */
    private static final String EXPRT_BTCH_ID = "EXPRT_BTCH_ID";
    /**
     * IMPR_BTCH_ID: 数据文件 => DataLake
     */
    private static final String IMPR_BTCH_ID = "IMPR_BTCH_ID";

    // 类型code / 类型名称
    private static final String[] SUBJECT_BALANCE = {"SBJ", "科目余额校验"};

    private static final String[] JOURNAL_BOOK = {"JE", "序时账校验"};

    private static final String CROSS_CHECK = "科目余额、序时账交叉校验";

    @Override
    @DynamicDS("projectId")
    public void exportCheckReport(String projectId, String requestCode, HttpServletRequest request, HttpServletResponse response) throws IOException {

        InputStream in = getClass().getClassLoader().getResourceAsStream("templateCheckReport.xlsx");
        OutputStream out = null;

        XSSFWorkbook wb = null;
        try {

            wb = new XSSFWorkbook(in);

            List<Boolean> flagList = new ArrayList<>();

            // 源系统ERP导出
            List<ExportCheck> fromErp = dataMapper.queryErpExport(projectId, EXPRT_BTCH_ID);
            boolean fromErpFlag = renderCheckExcel(wb, fromErp, 1);
            flagList.add(fromErpFlag);

            // ERP导入DataLake
            List<ExportCheck> toDataLake = dataMapper.queryErpExport(projectId, IMPR_BTCH_ID);
            boolean toDataLakeFlag = renderCheckExcel(wb, toDataLake, 2);
            flagList.add(toDataLakeFlag);

            // 1-科目余额
            List<Map<String, Object>> sbjResult = dataMapper.queryCheckReport(requestCode, SUBJECT_BALANCE[0]);
            boolean sbjFlag = exportToExcel(wb, sbjResult, 3, SUBJECT_BALANCE[1], CHECK_SHEET_TITLE);
            flagList.add(sbjFlag);

            // 2-序时账
            List<Map<String, Object>> jeResult = dataMapper.queryCheckReport(requestCode, JOURNAL_BOOK[0]);
            boolean jeFlag = exportToExcel(wb, jeResult, 4, JOURNAL_BOOK[1], CHECK_SHEET_TITLE);
            flagList.add(jeFlag);

            // 3-科目余额、序时账交叉校验
            List<Map<String, Object>> multiResult = dataMapper.queryCheckReportIntersect(requestCode);
            boolean multiFlag = exportToExcel(wb, multiResult, 5, CROSS_CHECK, INTERSECTR_SHEET_TITLE);
            flagList.add(multiFlag);

            // 生成校验结果封面表
            genIndexSheet(wb, flagList);

            for (int i = 0; i < wb.getNumberOfSheets(); i++) {
                XSSFSheet sheetAt = wb.getSheetAt(i);
                int columnNum = sheetAt.getRow(0).getLastCellNum();
                for (int jc = 0; jc < columnNum; jc++) {
                    sheetAt.autoSizeColumn(jc);
                }
            }

            ResponseUtils.setZipDownloadResponseHeader("校验报告.xlsx", request, response);
            out = response.getOutputStream();
            wb.write(out);
            out.flush();

        } finally {
            if (out != null) {
                out.close();
            }
        }
    }

    // 判断表是否全部校验通过
    private void genIndexSheet(XSSFWorkbook wb, List<Boolean> flagList) {
        XSSFSheet sheet = wb.getSheetAt(0);
        XSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        String result = "否";
        for (int i = 0; i < flagList.size(); i++) {
            if (flagList.get(i)) {
                result = "是";
            }
            XSSFCell cell = sheet.getRow(i + 4).createCell(1);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(result);
        }
    }

    /**
     * 科目余额 序时账校验sheet页
     *
     * @param wb
     * @param data
     * @param sheetIndex
     * @param sheetName
     * @param tableHeader
     */
    private static boolean exportToExcel(XSSFWorkbook wb, List<Map<String, Object>> data, int sheetIndex, String sheetName, String[] tableHeader) {
        boolean flag = true;
        try {
            wb.createSheet();
            wb.setSheetName(sheetIndex, sheetName);
            XSSFSheet sheet = wb.getSheetAt(sheetIndex);


            XSSFCellStyle cellStyle = wb.createCellStyle();
            XSSFDataFormat dataFormat = wb.createDataFormat();

            for (int r = 0; r <= data.size(); r++) {
                XSSFRow row = sheet.createRow(r);
                // 表头渲染
                if (r == 0) {
                    for (int c = 0; c < tableHeader.length; c++) {
                        XSSFCell cell = row.createCell(c);
                        cell.setCellValue(tableHeader[c]);
                    }
                } else {
                    flag = renderData(data, tableHeader, cellStyle, dataFormat, r, row);
                }
            }

        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        }

        return flag;
    }

    /**
     * 渲染data
     *
     * @param data
     * @param tableHeader
     * @param cellStyle
     * @param dataFormat
     * @param r
     * @param row
     */
    private static boolean renderData(List<Map<String, Object>> data, String[] tableHeader, XSSFCellStyle cellStyle, XSSFDataFormat dataFormat, int r, XSSFRow row) {
        boolean flag = true;
        // 数据渲染
        Map<String, Object> result = data.get(r - 1);
        for (int c = 0; c < tableHeader.length; c++) {
            XSSFCell cell = row.createCell(c);
            Object obj = result.get(tableHeader[c]);
            // 字符串类型和数值类型分开处理
            if (obj instanceof BigDecimal) {
                cellStyle.setDataFormat(dataFormat.getFormat("_(* #,##0.00_);_(* (#,##0.00);_(* \"\"-\"\"??_);_(@_)"));
                cell.setCellStyle(cellStyle);
                if (((BigDecimal) obj).compareTo(BigDecimal.ZERO) != 0) {
                    flag = false;
                }
                cell.setCellValue(Double.parseDouble(obj.toString()));
            } else {
                cell.setCellValue(obj.toString());
            }
        }

        return flag;
    }

    /**
     * 渲染导入/导出数据校验 sheet页面
     *
     * @param wb
     * @param data
     * @param sheetIndex
     */
    private boolean renderCheckExcel(XSSFWorkbook wb, List<ExportCheck> data, int sheetIndex) {

        boolean flag = true;

        if (data.isEmpty()) {
            return flag;
        }

        XSSFSheet sheet = wb.getSheetAt(sheetIndex);

        // 会计年度
        sheet.getRow(0).createCell(1).setCellValue(data.get(0).getYearRange());
        // 批次号
        sheet.getRow(1).createCell(1).setCellValue(data.get(0).getBatchNo());
        // 操作日期
        sheet.getRow(2).createCell(1).setCellValue(data.get(0).getOperDate());
        // 操作人员
        sheet.getRow(3).createCell(1).setCellValue(data.get(0).getOperator());

        // 渲染数据
        for (int i = 7; i < data.size() + 7; i++) {
            XSSFRow row = sheet.createRow(i);
            int j = i - 7;
            row.createCell(0).setCellValue(data.get(j).getTableName());
            row.createCell(1).setCellValue(data.get(j).getContent());
            row.createCell(2).setCellValue(data.get(j).getOrigin());
            row.createCell(3).setCellValue(data.get(j).getTarget());
            String result = data.get(j).getCheckResult();
            if (!"通过".equals(result)) {
                flag = false;
            }
            row.createCell(4).setCellValue(result);
        }

        return flag;
    }

}
